# coding = utf-8
# @Time    : 2024-07-21  16:56:52
# @Author  : zhaosheng@nuaa.edu.cn
# @Describe: DingDing Utils.

import cfg
import json
import redis
import pymysql
from datetime import datetime, timedelta

DB_CONFIG = cfg.DB_CONFIG

from dagent_llm import LLM
model = LLM(llm_server=cfg.LLM["llm_server"])

# Redis连接池, decode_responses=True表示将返回的byte类型数据解码为str类型
# 读取cfg制定的redis配置
pool = redis.ConnectionPool(host=cfg.REDIS_HOST, port=cfg.REDIS_PORT, db=cfg.REDIS_DB, decode_responses=True)
r = redis.Redis(connection_pool=pool)

def connect_to_database():
    try:
        connection = pymysql.connect(**DB_CONFIG)
        return connection
    except Exception as e:
        print(f"Error connecting to MySQL database: {e}")
        return None

def close_connection(connection):
    try:
        connection.close()
    except Exception as e:
        print(f"Error closing MySQL connection: {e}")

def get_employee_info(input):
    name = input["name"]
    print(f"\t*🔎 开始查看 {name} 的员工信息 ...")
    connection = connect_to_database()
    if not connection:
        return {"message": "数据库连接失败，请稍后重试"}
    try:
        query = f"""
        SELECT 
            name, 
            department, 
            job, 
            job_description, 
            birthday, 
            phone, 
            email, 
            place_of_origin,
            id_num
        FROM 
            employee_info 
        WHERE 
            name = '{name}'
        """
        with connection.cursor() as cursor:
            cursor.execute(query)
            result = cursor.fetchone()
        if result:
            name, department, job, job_description, birthday, phone, email, place_of_origin,id_num = result
            info = {
                "姓名": name,
                "部门": department,
                "职位": job,
                "职位描述": job_description,
                "生日": birthday,
                "电话号码": phone,
                "电子邮件": email,
                "籍贯": place_of_origin,
                "身份证号": id_num
            }
            # get attendance data
            attendance = get_attendance({"name": name})
            # merge result to single dict
            info.update(attendance)
        else:
            info = {"message": f"未找到姓名为 {name} 的员工信息"}
    except Exception as e:
        print(f"Error executing MySQL query: {e}")
        info = {"message": "查询失败，请稍后重试"}
    finally:
        close_connection(connection)
    return info

#考勤查询，输入员工姓名与查询月份，数据表定时每月1日更新上月考勤数据
def get_attendance(input):
    name = input["name"]
    now_month = datetime.now().month - 1
    now_month = str(now_month).zfill(2)
    now_year = datetime.now().year
    year_month = input.get("yearMonth", f"{now_year}-{now_month}").strip()
    print(f"\t*🔎 开始查询 {name} 在 {year_month} 的个人信息和考勤等指标 ...")
    connection = connect_to_database()
    if not connection:
        return {"message": "数据库连接失败，请稍后重试"}
    year, month = map(int, year_month.split('-'))
    start_date = datetime(year, month, 1).strftime('%Y-%m')
    end_date = (datetime(year, month, 1) + timedelta(days=31)).strftime('%Y-%m')  # 假设每月最多31天
    # 查询考勤信息
    query = f"""
    SELECT 
        name, 
        date, 
        out_times, 
        leave_times, 
        lateness_times, 
        earlyDeparture_times, 
        businessTrips_times
    FROM 
        attendance 
    WHERE 
        name = '{name}' AND 
        date >= '{start_date}' AND
        date <= '{end_date}'
    """
    try:
        with connection.cursor() as cursor:
            cursor.execute(query)
            result = cursor.fetchone()

        if result:
            name, date, outTimes, leaveTimes, latenessTimes, earlyDepartureTimes, businessTripsTimes = result
            
            # 格式化查询结果
            info = {
                "姓名": name,
                "年月": year_month,
                "外出次数": outTimes,
                "请假次数": leaveTimes,
                "迟到次数": latenessTimes,
                "早退次数": earlyDepartureTimes,
                "出差次数": businessTripsTimes,
            }
        else:
            print(f"No attendance record found for {name} in {year_month}")
            info = {"message": f"{name} 在 {year_month} 未找到相关考勤记录"}
    except Exception as e:
        print(f"Error executing MySQL query: {e}")
        info = {"message": "查询失败，请稍后重试"}
    return info

#查询日志，根据员工姓名与日期
def get_daily_logs(input):
    name = input["name"]
    date = input["start_date"]
    end_date = input["end_date"]
    # get datelist
    start_date = datetime.strptime(start_date, "%Y-%m-%d").date()
    end_date = datetime.strptime(end_date, "%Y-%m-%d").date()
    date_list = [start_date + timedelta(days=x) for x in range((end_date - start_date).days + 1)]
    print(date_list)
    print(f"\t*🔎 开始查看 {name} {start_date}~{end_date} 日志")
    connection = connect_to_database()
    if not connection:
        return {"message": "数据库连接失败，请稍后重试"}
    all_data = {}
    if len(date_list) > 31:
        date_list = date_list[-31:]
        print(f"查询日期范围过大，仅查询最近31天的日志")
    try:
        for date in date_list:
            date = date.strftime("%Y-%m-%d")
            # 查询日报信息
            query = f"""
            SELECT 
                name, 
                date, 
                completed_work, 
                unfinished_work, 
                coordination_work 
            FROM 
                daily_logs 
            WHERE 
                name = '{name}' AND 
                DATE(log_date) = '{date}'
            """
            with connection.cursor() as cursor:
                cursor.execute(query)
                results = cursor.fetchall()
            if results:
                # 格式化查询结果
                logs = []
                for result in results:
                    name,date,completed_work,unfinished_work,coordination_work = result
                    logs.append({
                        "完成的工作内容": completed_work,
                        "未完成的工作内容": unfinished_work,
                        "需要协调的工作内容": coordination_work,
                        "提交时间": date.strftime("%Y-%m-%d %H:%M:%S")
                    })
                info = {
                    "姓名": name,
                    "日报": logs
                }
            else:
                info = {"message": f"{name} 在 {start_date}~{end_date} 未查询到日报"}
            all_data[date] = info        
    except Exception as e:
        print(f"Error executing MySQL query: {e}")
        all_data = {"message": "查询失败，请稍后重试"}
    finally:
        close_connection(connection)
    return all_data

#钉钉运动步数查询，每月1日自动查询上月每天步数
def get_step_count(input):
    name = input["name"]
    # if start_date is not exist, return current date
    end_date = input.get("end_date", datetime.now().strftime("%Y-%m-01"))
    # if end_date is not exist, return the first day of last month
    start_date = input.get("start_date", (datetime.now() - timedelta(days=datetime.now().day)).strftime("%Y-%m-01"))

    print(f"\t*🔎 开始查看 {name} 在 {start_date} 到 {end_date} 的步数统计 ...")

    connection = connect_to_database()
    if not connection:
        return {"message": "数据库连接失败，请稍后重试"}

    try:
        # 查询步数统计信息
        query = f"""
        SELECT 
            name, 
            date, 
            stepCount
        FROM 
            step_count 
        WHERE 
            name = '{name}' AND 
            date >= '{start_date}' AND
            date <= '{end_date}'
        """
        with connection.cursor() as cursor:
            cursor.execute(query)
            results = cursor.fetchall()

        total_steps = 0
        steps_by_day = {}

        if results:
            for result in results:
                name, stat_date, step_count = result
                total_steps += step_count
                steps_by_day[stat_date.strftime("%Y-%m-%d")] = step_count

            info = {
                "姓名": name,
                "开始日期": start_date,
                "结束日期": end_date,
                "总步数": total_steps,
                # "每日步数": steps_by_day
            }
        else:
            info = {"message": f"{name} 在 {start_date} 到 {end_date} 之间未查询到步数统计"}

    except Exception as e:
        print(f"Error executing MySQL query: {e}")
        info = {"message": "查询失败，请稍后重试"}

    finally:
        close_connection(connection)

    return info

#根据姓名和年月查询员工绩效得分
def get_employee_performance(input):
    name = input["name"]
    # if no year_month, return last month
    year_month = input.get("yearMonth", (datetime.now() - timedelta(days=datetime.now().day)).strftime("%Y-%m"))
    print(f"\t*🔎 开始查询 {name} 在 {year_month} 的员工绩效 ...")
    connection = connect_to_database()
    if not connection:
        return {"message": "数据库连接失败，请稍后重试"}
    try:
        # 查询员工绩效
        query = f"""
        SELECT 
            name, 
            date, 
            performance, 
            department
        FROM 
            employee_performance 
        WHERE 
            name = '{name}' AND
            date = '{year_month}'
        """
        with connection.cursor() as cursor:
            cursor.execute(query)
            result = cursor.fetchone()

        if result:
            employeeName, yearMonth, performance, department = result
            # 查询绩效目标
            goal = get_goal({"name": name, "year": year_month.split('-')[0], "month": year_month.split('-')[1]})
            info = {
                "员工姓名": employeeName,
                "年月": yearMonth,
                "绩效": performance,
                "部门": department,
                "绩效目标": goal.get("绩效目标", "未查询到绩效目标")
            }
        else:
            info = {"message": f"{name} 在 {year_month} 未查询到员工绩效"}
    except Exception as e:
        print(f"Error executing MySQL query: {e}")
        info = {"message": "查询失败，请稍后重试"}

    finally:
        close_connection(connection)
    return info

#项目信息查询
def get_project_info(input):
    project_name = input["projectName"]
    print(f"\t*🔎 开始查询项目信息：{project_name} ...")
    connection = connect_to_database()
    if not connection:
        return {"message": "数据库连接失败，请稍后重试"}
    try:
        # 查询项目信息
        query = f"""
        SELECT 
            projectname, 
            start_date, 
            end_date, 
            status, 
            teamMembers, 
            progress
        FROM 
            project_information 
        WHERE 
            projectname = '{project_name}'
        """
        with connection.cursor() as cursor:
            cursor.execute(query)
            result = cursor.fetchone()
        if result:
            # 格式化查询结果
            projectname, startDate, endDate, status, teamMembers, progress = result
            info = {
                "项目名称": projectname,
                "起始时间": startDate.strftime("%Y-%m-%d"),
                "结束时间": endDate.strftime("%Y-%m-%d") if endDate else None,
                "项目状态": status,
                "项目人员": teamMembers.split(",") if teamMembers else [],
                "项目进展": progress
            }
        else:
            info = {"message": f"未找到名称为 {project_name} 的项目信息"}
    except Exception as e:
        print(f"Error executing MySQL query: {e}")
        info = {"message": "查询失败，请稍后重试"}
    finally:
        close_connection(connection)
    return info

# 查询员工在某个年月的绩效目标
def get_goal(input):
    name = input["name"]
    year = input.get("year", datetime.now().year)
    month = input.get("month", datetime.now().month)
    month = str(month).zfill(2)
    print(f"\t*🔎 开始查询 {name} 在 {year}-{month} 的绩效目标 ...")
    connection = connect_to_database()
    if not connection:
        return {"message": "数据库连接失败，请稍后重试"}
    try:
        # 查询绩效目标
        query = f"""
        SELECT 
            name,
            goal
        FROM 
            employee_goal 
        WHERE 
            date = '{year}-{month}'
        """
        with connection.cursor() as cursor:
            cursor.execute(query)
            result = cursor.fetchone()
        if result:
            name, goal = result
            info = {
                "姓名": name,
                "年月": f"{year}-{month}",
                "绩效目标": goal
            }
        else:
            info = {"message": f"{name} 在 {year}-{month} 未查询到绩效目标"}
    except Exception as e:
        print(f"Error executing MySQL query: {e}")
        info = {"message": "查询失败，请稍后重试"}
    finally:
        close_connection(connection)
    return info

# 返回所有项目名称
def get_projects():
    # 查询project_information中projectname列，去重后返回列表
    connection = connect_to_database()
    if not connection:
        return []
    try:
        query = f"""
        SELECT DISTINCT projectname FROM project_information
        """
        with connection.cursor() as cursor:
            cursor.execute(query)
            results = cursor.fetchall()
        projects = [result[0] for result in results]
    except Exception as e:
        print(f"Error executing MySQL query: {e}")
        projects = []
    finally:
        close_connection(connection)

    return projects

# 返回所有成员名称
def get_members():
    # members = ["赵胜", "张龙", "谢羽凯", "李四", "王五", "赵六", "张七", "李八"]
    # 查询 employee_info中name列，去重后返回列表
    connection = connect_to_database()
    if not connection:
        return []
    try:
        query = f"""
        SELECT DISTINCT name FROM employee_info
        """
        with connection.cursor() as cursor:
            cursor.execute(query)
            results = cursor.fetchall()
        members = [result[0] for result in results]
    except Exception as e:
        print(f"Error executing MySQL query: {e}")
        members = []
    finally:
        close_connection(connection)
    return members

# 返回所有部门名称
def get_departments():
    # 查询 employee_info中department列，去重后返回列表
    connection = connect_to_database()
    if not connection:
        return []
    try:
        query = f"""
        SELECT DISTINCT department FROM employee_info
        """
        with connection.cursor() as cursor:
            cursor.execute(query)
            results = cursor.fetchall()
        departments = [result[0] for result in results]
    except Exception as e:
        print(f"Error executing MySQL query: {e}")
        departments = []
    finally:
        close_connection(connection)
    return departments

# 根据关键字查询知识库
def search_database(input):
    keyword = input["keyword"]
    print(f"\t*🔎 在知识库内查询 {keyword} ...")
    result = {
            "内容1": "盯小语项目",
            "内容2": "盯小语项目是一个语音识别项目",
            "内容3": "盯小语项目成员有赵胜，张龙，李四"
        }
    return {}

def auto_search(input):
    # assert string
    input = input["name"]
    assert isinstance(input, str)
    if input in get_departments():
        return {}
    elif input in get_projects():
        project_info = get_project_info({"projectName": input})
        # 一周前
        start_date = (datetime.now() - timedelta(days=7)).strftime("%Y-%m-%d")
        # 一天前
        end_date = (datetime.now() - timedelta(days=1)).strftime("%Y-%m-%d")
        project_progress = search_logs({"keyword": input, "start_date": start_date, "end_date": end_date})
        data = {
            "项目信息": project_info,
            "项目进展": project_progress
        }
        return data
    elif input in get_members():
        return get_employee_info({"name": input})
    else:
        # 一周前
        start_date = (datetime.now() - timedelta(days=7)).strftime("%Y-%m-%d")
        # 一天前
        end_date = (datetime.now() - timedelta(days=1)).strftime("%Y-%m-%d")
        project_progress = search_logs({"keyword": input, "start_date": start_date, "end_date": end_date})
        database_data = search_database({"keyword": input})
        data = {
            "项目进展": project_progress,
            "知识库": database_data
        }
        return data

# 在日志中搜索关键字
def search_logs(input):
    # 根据关键字查询日志
    keyword = input["keyword"]
    start_date = input["start_date"]
    end_date = input["end_date"]
    # get datelist
    start_date = datetime.strptime(start_date, "%Y-%m-%d").date()
    end_date = datetime.strptime(end_date, "%Y-%m-%d").date()
    connection = connect_to_database()
    if not connection:
        return {"message": "数据库连接失败，请稍后重试"}
    all_data = []
    try:  
        query = f"""
        SELECT 
            name, 
            date, 
            completed_work, 
            unfinished_work, 
            coordination_work 
        FROM 
            daily_logs 
        WHERE 
            (completed_work LIKE '%{keyword}%' OR
            unfinished_work LIKE '%{keyword}%' OR
            coordination_work LIKE '%{keyword}%') AND
            date > '{start_date}' AND
            date < '{end_date}'
        ORDER BY date DESC
        """
        with connection.cursor() as cursor:
            cursor.execute(query)
            results = cursor.fetchall()
        if results:
            # 格式化查询结果
            logs = []
            for result in results:
                name,date,completed_work,unfinished_work,coordination_work = result
                for _data in completed_work.split("\n"):
                    if keyword in _data:
                        completed_work = _data
                        break
                # completed_work 解码为utf-8
                completed_work = completed_work[:50]
            
                all_data.append(f'{name}在{date.strftime("%Y-%m-%d")}完成{completed_work}')
        else:
            info = {"message": f"{name} 在 {start_date}~{end_date} 未查询到日报"}
    except Exception as e:
        print(f"Error executing MySQL query: {e}")
        all_data = {"message": "查询失败，请稍后重试"}
    finally:
        close_connection(connection)
    # 将all_data转为字符串，并最多1000字
    # all_data = json.dumps(all_data)
    # if len(all_data) > 1000:
    #     all_data = all_data[:1000]
    print(all_data)
    return all_data

# 保存数据到Redis，如果存在则更新
def save_to_redis(key, value):
    r.set(key, json.dumps(value))

# 从Redis中获取数据
def get_from_redis(key,default_value):
    value = r.get(key)
    print(f"Get Value: {value}")
    if value is not None:
        return json.loads(value)
    else:
        return default_value
    
# 从Redis中删除数据
def delete_from_redis(key):
    r.delete(key)

# 从Redis中获取所有的key
def get_all_keys_from_redis():
    return r.keys()

def get_history_by_user_id(user_id):
    return get_from_redis(user_id, [])

def set_history_by_user_id(user_id, history):
    save_to_redis(user_id, history)

ALL_EMPLOYEES = get_members()
ALL_PROJECTS = get_projects()
ALL_DEPARTMENTS = get_departments()

if __name__ == '__main__':
    print(auto_search({"name": "盯小语"}))
    print(auto_search({"name": "赵胜"}))